package com.wustl.android.hood;

import java.io.FileNotFoundException;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

enum MAP_TYPE { MT_MAP, MT_SATELLITE, MT_UNKNOWN };

public class DbLocations {
	public static final String KEY_ROWID="_id";
    public static final String KEY_TITLE="title";
    public static final String KEY_TYPE="type";
    public static final String KEY_CREATEDATE="create_date";
    public static final String KEY_LATITUDE = "latitude";
    public static final String KEY_LONGITUDE = "longitude";
    public static final String KEY_ZOOM	= "zoom";
    public static final String KEY_MAPTYPE = "map_type";
    
    
    /**
     * Database creation sql statement
     */
    private static final String DATABASE_CREATE =
        "create table locations (_id integer primary key autoincrement, "
            + KEY_TITLE + " text not null, "
            + KEY_TYPE +" integer default 0, "
            + KEY_CREATEDATE + " real default CURRENT_TIMESTAMP, "
            + KEY_LATITUDE +" double default 0, "
            + KEY_LONGITUDE +" double default 0, "
            + KEY_MAPTYPE + " integer default 0, "
            + KEY_ZOOM + " integer default 8);";

    private static final String DATABASE_NAME = "hood";
    private static final String DATABASE_TABLE = "locations";
    private static final int DATABASE_VERSION = 2;

    private SQLiteDatabase mDb;
    private final Context mCtx;
    
    public DbLocations(Context ctx) {
        this.mCtx = ctx;
    }
    
    public static MAP_TYPE convertMapType(int type) {
    	switch(type) {
    	case 0:    		return MAP_TYPE.MT_MAP;
    	case 1:			return MAP_TYPE.MT_SATELLITE;
    	default:		return MAP_TYPE.MT_UNKNOWN;
    	}
    }
    
    public static int convertMapType(MAP_TYPE type) {
    	if( type == MAP_TYPE.MT_MAP )
    		return 0;
    	else if( type == MAP_TYPE.MT_SATELLITE ) 
    		return 1;
    	else
    		return -1;
    }
    
    public DbLocations open() throws SQLException {
        try {
            mDb = mCtx.openDatabase(DATABASE_NAME, null);
        } catch (FileNotFoundException e) {
            try {
                mDb = mCtx.createDatabase(DATABASE_NAME, DATABASE_VERSION, 0, null);
                mDb.execSQL(DATABASE_CREATE);
            } catch (FileNotFoundException e1) {
                throw new SQLException("Could not create database");
            }
        }
        return this;
    }
    
    public DbLocations init() throws SQLException {
    	try {
    		mCtx.deleteDatabase( DATABASE_NAME );
    		mDb = mCtx.createDatabase(DATABASE_NAME, DATABASE_VERSION, 0, null);
    		mDb.execSQL(DATABASE_CREATE);
    	} catch (FileNotFoundException e) {
    		throw new SQLException("Could not create database");
    	}
    	
    	return this;
    }

    public void close() {
        mDb.close();
    }

    /**
     * Create a new location using the title and GPS locations provided.
     * If the location is successfully created return the new rowId, otherwise return -1
     * @param title	the tile of the location
     * @param longitude	longitude of the location
     * @param latitude	latitude of the location
     * @param type	type of this location
     * @return
     */
    public long createLocation(String title, double latitude, double longitude, int type, int zoom, int map_type) {
    	ContentValues initialValues = new ContentValues();
    	initialValues.put(KEY_TITLE, title);
    	initialValues.put(KEY_LATITUDE, latitude);
    	initialValues.put(KEY_LONGITUDE, longitude);
    	initialValues.put(KEY_TYPE, type);
    	initialValues.put(KEY_ZOOM, zoom);
    	initialValues.put(KEY_MAPTYPE, map_type);
    	return mDb.insert(DATABASE_TABLE, null, initialValues);
    }

    /**
     * Delete the location with the given rowId
     * @param rowId id of location to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteLocation(long rowId) {
        return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }

    /**
     * Return a Cursor over the list of all locations in the database
     * @return Cursor over all locations
     */
    public Cursor fetchAllLocations() {
        return mDb.query(DATABASE_TABLE, new String[] {
                KEY_ROWID, KEY_TITLE, KEY_CREATEDATE, KEY_LATITUDE, KEY_LONGITUDE, KEY_ZOOM, KEY_MAPTYPE, KEY_TYPE}, null, null, null, null, null);
    }
    
    public Cursor fetchAllLocations( String[] columns) {
    	return mDb.query(DATABASE_TABLE, columns, null, null, null, null, null);
    }
    
    public Cursor fetchAllLocationsID() {
    	return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID}, null, null, null, null, null);
    }

    /**
     * Return a Cursor positioned at the location that matches the given rowId
     * @param rowId id of location to retrieve
     * @return Cursor positioned to matching location, if found
     * @throws SQLException if note could not be found/retrieved
     */
    public Cursor fetchLocation(long rowId) throws SQLException {
        Cursor result = mDb.query(true, DATABASE_TABLE, new String[] {
                KEY_ROWID, KEY_TITLE, KEY_CREATEDATE, KEY_LONGITUDE, KEY_LATITUDE, KEY_ZOOM, KEY_MAPTYPE, KEY_TYPE}, KEY_ROWID + "=" + rowId, null, null,
                null, null);
        if ((result.count() == 0) || !result.first()) {
            throw new SQLException("No location matching ID: " + rowId);
        }
        return result;
    }

    public boolean updateLocation(long rowId, String title, double longitude, double latitude, int zoom, int type, int map_type) {
        ContentValues args = new ContentValues();
        args.put(KEY_TITLE, title);
        args.put(KEY_LONGITUDE, longitude);
        args.put(KEY_LATITUDE, latitude);
        args.put(KEY_TYPE, type);
        args.put(KEY_ZOOM, zoom);
        args.put(KEY_MAPTYPE, map_type);
        return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }
}
